import json
import os

from Demos.win32ts_logoff_disconnected import username
from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from pandas.core.methods.to_dict import to_dict
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///WordCrush.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(256), nullable=False)
    def set_password(self, raw_password):
        self.password = generate_password_hash(raw_password)
    def check_password(self, raw_password):
        return check_password_hash(self.password, raw_password)


class GameRecord(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), unique=False, nullable=False)
    gameType = db.Column(db.Integer, unique=False, nullable=False)
    score = db.Column(db.Integer, nullable=False)
    time = db.Column(db.String(25), unique=False, nullable=False)
    learnedWords_json = db.Column(db.Text, unique=False, nullable=False)

    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'gameType': self.gameType,
            'score': self.score,
            'time': self.time,
            'learnedWords': self.learnedWords
        }

    def to_dict_rank(self):
        return {
            'username': self.username,
            'score': self.score,
            'time': self.time,
        }

    @property
    def learnedWords(self):
        return json.loads(self.learnedWords_json)

    @learnedWords.setter
    def learnedWords(self, value):
        self.learnedWords_json = json.dumps(value)


with app.app_context():
    db.create_all()

@app.route('/')
def home():
    return "Hello, Flask! Welcome World Crush!"

@app.route('/api/hello', methods=['GET'])
def hello():
    name = request.args.get('name', 'World')
    return jsonify({"message": f"Hello, {name}!"})

@app.route('/api/register', methods=['POST'])
def register():
    data = request.get_json()
    if not data:
        return jsonify({'status': 'fail', 'message': '请求体不能为空'})
    username = data.get('username')
    password = data.get('password')

    if not username or not password:
        print("用户名和密码不能为空")
        return jsonify({'status': 'fail', 'message': '用户名和密码不能为空'})

    if User.query.filter_by(username=username).first():
        print("用户名已存在")
        return jsonify({'status': 'fail', 'message': '用户名已存在'})

    user = User(username=username)
    user.set_password(password)
    db.session.add(user)
    db.session.commit()

    return jsonify({'status': 'success', 'message': '注册成功'})

@app.route('/api/login', methods=['POST'])
def login():
    data = request.get_json()
    if not data:
        return jsonify({'status': 'fail', 'message': '请求体不能为空'})
    username = data.get('username')
    password = data.get('password')

    if not username or not password:
        return jsonify({'status': 'fail', 'message': '用户名和密码不能为空'})

    user = User.query.filter_by(username=username).first()
    if user and user.check_password(password):
        return jsonify({'status': 'success', 'message': '登录成功'})
    else:
        return jsonify({'status': 'fail', 'message': '用户名或密码错误'})


@app.route('/api/getAllGameRecord', methods=['POST'])
def get_game_record():
    data = request.get_json()
    username = data.get('username')
    if not username:
        return jsonify({'status': 'fail', 'message': '用户名不能为空'})

    game_records = GameRecord.query.filter_by(username=username).all()

    if not game_records:
        return jsonify({'status': 'fail', 'message': '未能找到游戏记录'})

    records_data = [record.to_dict() for record in game_records]

    return jsonify({'status': 'success', 'message': records_data})

@app.route('/api/addGameRecord', methods=['POST'])
def add_game_record():
    data = request.get_json()
    if not data:
        return jsonify({'status': 'fail', 'message': '请求体不能为空'})
    username = data.get('username')
    gameType = data.get('gameType')
    score = data.get('score')
    time = data.get('time')
    learnedWords = data.get('learnedWords')

    try:
        new_record = GameRecord(
            username=username,
            gameType=bool(gameType),
            score=score,
            time=time
        )
        new_record.learnedWords = learnedWords
        db.session.add(new_record)
        db.session.commit()
        return jsonify({'status': 'success', 'message': '数据上传云端成功'})
    except Exception as e:
        return jsonify({'status': 'fail', 'message': '数据上传云端成功' + str(e)})

@app.route('/api/deleteGameRecord', methods=['POST'])
def delete_game_record():
    data = request.get_json()
    username = data.get('username')
    gameType = data.get('gameType')
    score = data.get('score')
    time = data.get('time')
    if not username or gameType is None or not score or not time:
        return jsonify({'status': 'fail', 'message': '参数错误！'})

    game_record = GameRecord.query.filter_by(username=username, gameType=gameType, score=score, time=time).first()
    if not game_record:
        return jsonify({'status': 'fail', 'message': '游戏记录不存在！'})
    try:
        db.session.delete(game_record)
        db.session.commit()
        return jsonify({'status': 'success', 'message': '云端记录删除成功！'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'fail', 'message': '云端记录删除失败！'})

UPLOAD_FOLDER = 'static/avatars'
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
ALLOWED_EXTENSIONS = {'jpg'}
def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
# 允许的文件扩展名

@app.route('/api/uploadAvatar', methods=['POST'])
def upload_avatar():
    if 'avatar' not in request.files or 'username' not in request.form:
        return jsonify({'status': 'success', 'message': '缺少文件或用户名'})

    file = request.files['avatar']
    username = request.form['username'].strip()

    if file and allowed_file(file.filename) and username:
        # 使用 secure_filename 防止路径注入
        ext = file.filename.rsplit('.', 1)[1].lower()
        filename = secure_filename(f"{username}.{ext}")
        file_path = os.path.join(UPLOAD_FOLDER, filename)
        file.save(file_path)

        return jsonify({'status': 'success', 'message': '上传成功'})
    else:
        return jsonify({'status': 'fail', 'message': '无效的文件或用户名'})

from sqlalchemy import func, desc
from sqlalchemy.orm import aliased
@app.route('/api/getTopNRecord', methods=['POST'])
def get_top_n_record():
    data = request.get_json()
    gameType = data.get('gameType')
    topN = data.get('topN')

    if gameType is None or not topN:
        return jsonify({'status': 'fail', 'message': '请求参数无效！'})

    # 子查询：每个用户名的最高分记录
    subquery = (
        db.session.query(
            GameRecord.username,
            func.max(GameRecord.score).label('max_score')
        )
        .filter_by(gameType=gameType)
        .group_by(GameRecord.username)
        .subquery()
    )

    # 再查询完整记录（包括 id / 时间等）
    # 用 username 和 score 去连接原始表，取完整记录
    GameRecordAlias = aliased(GameRecord)
    top_records = (
        db.session.query(GameRecordAlias)
        .join(subquery, (GameRecordAlias.username == subquery.c.username) &
                        (GameRecordAlias.score == subquery.c.max_score))
        .filter(GameRecordAlias.gameType == gameType)
        .order_by(GameRecordAlias.score.desc())
        .limit(int(topN))
        .all()
    )

    message = [r.to_dict_rank() for r in top_records]
    return jsonify({'status': 'success', 'message': message})

@app.route('/api/changePassword', methods=['POST'])
def change_password():
    data = request.get_json()
    username = data.get('username')
    oldPassword = data.get('oldPassword')
    newPassword = data.get('newPassword')
    if not username or not oldPassword or not newPassword:
        return jsonify({'status': 'fail', 'message': '参数不能为空！'})

    user = User.query.filter_by(username=username).first()
    if not user:
        return jsonify({'status': 'fail', 'message': '找不到该用户！'})

        # 检查旧密码
    if not user.check_password(oldPassword):
        return jsonify({'status': 'fail', 'message': '旧密码错误！'})

        # 设置新密码并保存
    user.set_password(newPassword)
    try:
        db.session.commit()  # 确保提交修改
        return jsonify({'status': 'success', 'message': '密码修改成功！'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'fail', 'message': '数据库错误，请重试！'})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)
